package aos.ydksxt.service.cxbdfx;

import aos.framework.core.typewrap.Dto;
import aos.framework.core.typewrap.Dtos;
import aos.framework.core.utils.AOSJson;
import aos.framework.core.utils.AOSUtils;
import aos.framework.web.router.HttpModel;
import aos.ydksxt.service.common.CommonService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.awt.*;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

/**
 * 金三国土采集信息比对分析服务
 * Created by Stiofan on 2017/3/28.
 */
@Service
public class G3GtcjxxbdfxService extends CommonService {
    @Override
    @Transactional
    public void initView(HttpModel httpModel) {
        httpModel.setAttribute("juid", httpModel.getInDto().get("juid"));
        httpModel.setViewPath("ydksxt/cxbdfx/g3gtcjxxbdfx.jsp");
    }

    @Transactional
    public void exportG3Gtcjxxbdfxjg(HttpModel httpModel) {
        Dto inDto = httpModel.getInDto();
        Dto params = Dtos.newDto();
        String gtcjxxSql = "with gtxx_ydks as\n" +
                " (select nvl(kz.shxydm, ry.nsrsbh) nsrsbh,\n" +
                "         td.tdzdh,\n" +
                "         ry.nsrmc,\n" +
                "         td.tdzmj,\n" +
                "         td.tdzyz,\n" +
                "         syq.tdsyq_ysje nynse\n" +
                "    from ydks.dj_tdjcxx    td,\n" +
                "         ydks.dj_tdsyqdjxx syq,\n" +
                "         ydks.dj_ryxx      ry,\n" +
                "         ydks.dj_ryxx_kz   kz,\n" +
                "         ydks.dj_wbdr_gtxx gt\n" +
                "   where td.tdbh = syq.tdbh\n" +
                "     and syq.tdsyrlx_dm = '01'\n" +
                "     and syq.djxh = ry.djxh\n" +
                "     and ry.djxh = kz.djxh\n" +
                "     and td.yxbz = 'Y'\n" +
                "     and syq.yxbz = 'Y'\n" +
                "     and td.tdzdh = gt.tdzdh\n" +
                "     and 'Y' = gt.yxbz\n";
        if (!StringUtils.isEmpty(inDto.getString("nsrsbh"))) {
            params.put("nsrsbh", inDto.get("nsrsbh"));
            gtcjxxSql += "   and (ry.nsrsbh = :nsrsbh or kz.shxydm = :nsrsbh)\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("nsrmc"))) {
            params.put("nsrmc", "%" + inDto.get("nsrmc") + "%");
            gtcjxxSql += "   and ry.nsrmc like :nsrmc\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("zgswjgDm"))) {
            params.put("zgswjgDm", inDto.get("zgswjgDm"));
            gtcjxxSql += "   and ry.zgswjg_dm = :zgswjgDm\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("zgswskfjDm"))) {
            params.put("zgswskfjDm", inDto.get("zgswskfjDm"));
            gtcjxxSql += "   and ry.zgswskfj_dm = :zgswskfjDm\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("ssglyDm"))) {
            params.put("ssglyDm", inDto.get("ssglyDm"));
            gtcjxxSql += "   and ry.ssgly_dm = :ssglyDm\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("tdzdh"))) {
            params.put("tdzdh", inDto.get("tdzdh"));
            gtcjxxSql += "   and td.tdzdh = :tdzdh\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("tdbh"))) {
            params.put("tdbh", inDto.get("tdbh"));
            gtcjxxSql += "   and td.tdbh = :tdbh\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("tdzdh"))) {
            params.put("tdzdh", inDto.get("tdzdh"));
            gtcjxxSql += "and td.tdzdh = :tdzdh\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("sqyqsrq"))) {
            params.put("sqyqsrq", inDto.getDate("sqyqsrq"));
            gtcjxxSql += "   and syq.tdsyq_qsrq >= :sqyqsrq\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("syqzzrq"))) {
            params.put("syqzzrq", inDto.getDate("syqzzrq"));
            gtcjxxSql += "   and syq.tdsyq_zzrq <= :syqzzrq";
        }
        gtcjxxSql += "     and td.tdzyz = gt.tdyz\n" +
                "     and td.tdzmj = gt.tdmj),\n" +
                "gtxx_jssj as\n" +
                " (select nvl(xx.tdsyqrnsrsbh, xx.nsrsbh) nsrsbh,\n" +
                "         xx.dh_1,\n" +
                "         xx.tdsyqrmc,\n" +
                "         xx.zytdmj1,\n" +
                "         null tdzyz,\n" +
                "         (nvl(ys.ystdmj, 0) - nvl(jm.jmmj, 0)) * ys.dwse jsse\n" +
                "    from hx_sb.SB_CXS_TDSYXXCJB xx,\n" +
                "         hx_sb.SB_CXS_TDSYYSXX  ys,\n" +
                "         hx_sb.SB_CXS_YSJMXZXX  jm\n" +
                "   where xx.syuuid = ys.syuuid(+)\n" +
                "     and ys.ysuuid = jm.ysuuid(+)\n" +
                "     and ys.yxbz(+) = 'Y'\n" +
                "     and jm.yxbz(+) = 'Y'\n" +
                "     and jm.yxqq(+) >= ys.yxqq\n" +
                "     and jm.yxqz(+) <= ys.yxqz\n" +
                "     and xx.yxbz = 'Y'\n" +
                "     and xx.sjgsdq like '26205%')\n" +
                "select ydks.*,\n" +
                "       jssj.nsrsbh js_nsrsbh,\n" +
                "       jssj.dh_1 js_tdzdh,\n" +
                "       jssj.tdsyqrmc js_nsrmc,\n" +
                "       jssj.zytdmj1 js_tdzmj,\n" +
                "       jssj.tdzyz js_tdzyz,\n" +
                "       jssj.jsse js_nynse,\n" +
                "       case\n" +
                "         when ydks.tdzmj = jssj.zytdmj1 and ydks.nynse = jssj.jsse then\n" +
                "          '正常'\n" +
                "         else\n" +
                "          '异常'\n" +
                "       end fxjg\n" +
                "  from gtxx_ydks ydks, gtxx_jssj jssj\n" +
                " where ydks.nsrsbh = jssj.nsrsbh\n";

        List<Map<String, Object>> gtcjxxes = persistenceDAO.findAllBySql(gtcjxxSql, params);
        Dto outDto = Dtos.newOutDto();
        if (gtcjxxes.size() == 0) {
            outDto.setAppCode("noXX");
            outDto.setAppMsg("根据所选条件未能获得相关数据!");
        } else {
            try {
                InputStream inputStream = getClass().getResourceAsStream("金三国土信息比对分析模板.xlsx");
                XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
                for (int i = 0; i < gtcjxxes.size(); i++) {
                    XSSFRow xssfRow = xssfSheet.getRow(i + 3);
                    if (xssfRow == null) {
                        xssfRow = xssfSheet.createRow(i + 3);
                        xssfRow.copyRowFrom(xssfSheet.getRow(3),new CellCopyPolicy());
                    }
                    Map map = gtcjxxes.get(i);
                    xssfRow.getCell(0).setCellValue(i + 1);
                    xssfRow.getCell(1).setCellValue(String.valueOf(map.get("nsrsbh")).replace("null",""));
                    xssfRow.getCell(2).setCellValue(String.valueOf(map.get("tdzdh")).replace("null",""));
                    xssfRow.getCell(3).setCellValue(String.valueOf(map.get("nsrmc")).replace("null",""));
                    xssfRow.getCell(4).setCellValue(String.valueOf(map.get("tdzmj")).replace("null",""));
                    xssfRow.getCell(5).setCellValue(String.valueOf(map.get("tdzyz")).replace("null",""));
                    xssfRow.getCell(6).setCellValue(String.valueOf(map.get("nynse")).replace("null",""));
                    xssfRow.getCell(7).setCellValue(String.valueOf(map.get("jsNsrsbh")).replace("null",""));
                    xssfRow.getCell(8).setCellValue(String.valueOf(map.get("jsTdzdh")).replace("null",""));
                    xssfRow.getCell(9).setCellValue(String.valueOf(map.get("jsNsrmc")).replace("null",""));
                    xssfRow.getCell(10).setCellValue(String.valueOf(map.get("jsTdzmj")).replace("null",""));
                    xssfRow.getCell(11).setCellValue(String.valueOf(map.get("jsTdzyz")).replace("null",""));
                    xssfRow.getCell(12).setCellValue(String.valueOf(map.get("jsNynse")).replace("null",""));
                    xssfRow.getCell(13).setCellValue(String.valueOf(map.get("fxjg")).replace("null",""));
                    XSSFCellStyle xssfCellStyle=xssfWorkbook.createCellStyle();
                    xssfCellStyle.cloneStyleFrom(xssfSheet.getRow(3).getCell(13).getCellStyle());
                    XSSFFont xssfFont=xssfWorkbook.createFont();
                    if ("异常".equals(String.valueOf(map.get("fxjg")).replace("null",""))) {
                        xssfFont.setColor(new XSSFColor(Color.RED));
                    } else {
                        xssfFont.setColor(new XSSFColor(Color.BLACK));
                    }
                    xssfCellStyle.setFont(xssfFont);
                    xssfRow.getCell(13).setCellStyle(xssfCellStyle);
                }
                File file = new File(httpModel.getRequest().getServletContext().getRealPath("download"),
                		//"bdjg_" + httpModel.getUserModel().getAccount() + "_jsgtcjxx.xlsx");
                "比对结果_" + httpModel.getUserModel().getAccount() + "_金三国土采集信息.xlsx");
                if (!file.exists()) {
                    file.createNewFile();
                } else {
                    file.delete();
                    file.createNewFile();
                }
                FileOutputStream fileOutputStream = new FileOutputStream(file);
                xssfWorkbook.write(fileOutputStream);
                fileOutputStream.flush();
                fileOutputStream.close();
                xssfWorkbook.close();
                outDto.setAppCode("suc");
                outDto.put("downloadUrl", "download/" + file.getName());
            } catch (IOException e) {
                outDto.setAppCode("err");
                outDto.setAppMsg("执行比对分析时出现异常：" + e.getMessage());
                logger.error(e);
            }
        }
        httpModel.setOutMsg(AOSJson.toJson(outDto));
    }
    @Transactional
    public void queryG3GtcjxxbdfxYcByParams(HttpModel httpModel) {
    	Dto inDto = httpModel.getInDto();
        Dto params = Dtos.newDto();
        String fgcjxxSql ="with gtxx_ydks as\n" +
                " (select nvl(kz.shxydm, ry.nsrsbh) nsrsbh,\n" +
                "         td.tdzdh,\n" +
                "         ry.nsrmc,\n" +
                "         td.tdzmj,\n" +
                "         td.tdzyz,\n" +
                "         syq.tdsyq_ysje nynse\n" +
                "    from ydks.dj_tdjcxx    td,\n" +
                "         ydks.dj_tdsyqdjxx syq,\n" +
                "         ydks.dj_ryxx      ry,\n" +
                "         ydks.dj_ryxx_kz   kz,\n" +
                "         ydks.dj_wbdr_gtxx gt\n" +
                "   where td.tdbh = syq.tdbh\n" +
                "     and syq.tdsyrlx_dm = '01'\n" +
                "     and syq.djxh = ry.djxh\n" +
                "     and ry.djxh = kz.djxh\n" +
                "     and td.yxbz = 'Y'\n" +
                "     and syq.yxbz = 'Y'\n" +
                "     and td.tdzdh = gt.tdzdh\n" +
                "     and 'Y' = gt.yxbz\n"+
                "     and td.tdzyz = gt.tdyz\n" +
                "     and td.tdzmj = gt.tdmj),\n" +
                "gtxx_jssj as\n" +
                " (select nvl(xx.tdsyqrnsrsbh, xx.nsrsbh) nsrsbh,\n" +
                "         xx.dh_1,\n" +
                "         xx.tdsyqrmc,\n" +
                "         xx.zytdmj1,\n" +
                "         null tdzyz,\n" +
                "         (nvl(ys.ystdmj, 0) - nvl(jm.jmmj, 0)) * ys.dwse jsse\n" +
                "    from hx_sb.SB_CXS_TDSYXXCJB xx,\n" +
                "         hx_sb.SB_CXS_TDSYYSXX  ys,\n" +
                "         hx_sb.SB_CXS_YSJMXZXX  jm\n" +
                "   where xx.syuuid = ys.syuuid(+)\n" +
                "     and ys.ysuuid = jm.ysuuid(+)\n" +
                "     and ys.yxbz(+) = 'Y'\n" +
                "     and jm.yxbz(+) = 'Y'\n" +
                "     and jm.yxqq(+) >= ys.yxqq\n" +
                "     and jm.yxqz(+) <= ys.yxqz\n" +
                "     and xx.yxbz = 'Y'\n" +
                "     and xx.sjgsdq like '26205%')\n" +
                "select * from(\n"+
                "select ydks.*,\n" +
                "       jssj.nsrsbh js_nsrsbh,\n" +
                "       jssj.dh_1 js_tdzdh,\n" +
                "       jssj.tdsyqrmc js_nsrmc,\n" +
                "       jssj.zytdmj1 js_tdzmj,\n" +
                "       jssj.tdzyz js_tdzyz,\n" +
                "       jssj.jsse js_nynse,\n" +
                "       case\n" +
                "         when ydks.tdzmj = jssj.zytdmj1 and ydks.nynse = jssj.jsse then\n" +
                "          '正常'\n" +
                "         else\n" +
                "          '异常'\n" +
                "       end fxjg\n" +
                "  from gtxx_ydks ydks, gtxx_jssj jssj\n" +
                " where ydks.nsrsbh = jssj.nsrsbh)t\n"+
                "where t.fxjg='异常'\n";
        
       // List<Map<String, Object>> fgcjxxes = persistenceDAO.findAllBySql(fgcjxxSql, params);
        List results = persistenceDAO.findAllBySql(fgcjxxSql, params);
        for (int i = 0; i < results.size(); i++) {
            Map map = (Map) results.get(i);
            map.put("lxrdz", getFullAddress((String) map.get("lxrdzXzqhdsDm"), (String) map.get("lxrdzXzqhxqDm"), (String) map.get("lxrdzJdxzDm"), (String) map.get("lxrdzSqjqDm"), (String) map.get("lxrdzDlxdDm"), (String) map.get("lxrdzLh")));
            map.put("zldz", getFullAddress((String) map.get("xzqhdszDm"), (String) map.get("xzqhxqDm"), (String) map.get("jdxzDm"), (String) map.get("zldzSqjqDm"), (String) map.get("zldzDlxdDm"), (String) map.get("zldzLh")));
        }
        httpModel.setOutMsg(AOSJson.toGridJson(AOSUtils.list2Map(results)));
    }
    @Transactional
    public void queryG3GtcjxxbdfxZcByParams(HttpModel httpModel) {
    	Dto inDto = httpModel.getInDto();
        Dto params = Dtos.newDto();
        String fgcjxxSql ="with gtxx_ydks as\n" +
                " (select nvl(kz.shxydm, ry.nsrsbh) nsrsbh,\n" +
                "         td.tdzdh,\n" +
                "         ry.nsrmc,\n" +
                "         td.tdzmj,\n" +
                "         td.tdzyz,\n" +
                "         syq.tdsyq_ysje nynse\n" +
                "    from ydks.dj_tdjcxx    td,\n" +
                "         ydks.dj_tdsyqdjxx syq,\n" +
                "         ydks.dj_ryxx      ry,\n" +
                "         ydks.dj_ryxx_kz   kz,\n" +
                "         ydks.dj_wbdr_gtxx gt\n" +
                "   where td.tdbh = syq.tdbh\n" +
                "     and syq.tdsyrlx_dm = '01'\n" +
                "     and syq.djxh = ry.djxh\n" +
                "     and ry.djxh = kz.djxh\n" +
                "     and td.yxbz = 'Y'\n" +
                "     and syq.yxbz = 'Y'\n" +
                "     and td.tdzdh = gt.tdzdh\n" +
                "     and 'Y' = gt.yxbz\n"+
                "     and td.tdzyz = gt.tdyz\n" +
                "     and td.tdzmj = gt.tdmj),\n" +
                "gtxx_jssj as\n" +
                " (select nvl(xx.tdsyqrnsrsbh, xx.nsrsbh) nsrsbh,\n" +
                "         xx.dh_1,\n" +
                "         xx.tdsyqrmc,\n" +
                "         xx.zytdmj1,\n" +
                "         null tdzyz,\n" +
                "         (nvl(ys.ystdmj, 0) - nvl(jm.jmmj, 0)) * ys.dwse jsse\n" +
                "    from hx_sb.SB_CXS_TDSYXXCJB xx,\n" +
                "         hx_sb.SB_CXS_TDSYYSXX  ys,\n" +
                "         hx_sb.SB_CXS_YSJMXZXX  jm\n" +
                "   where xx.syuuid = ys.syuuid(+)\n" +
                "     and ys.ysuuid = jm.ysuuid(+)\n" +
                "     and ys.yxbz(+) = 'Y'\n" +
                "     and jm.yxbz(+) = 'Y'\n" +
                "     and jm.yxqq(+) >= ys.yxqq\n" +
                "     and jm.yxqz(+) <= ys.yxqz\n" +
                "     and xx.yxbz = 'Y'\n" +
                "     and xx.sjgsdq like '26205%')\n" +
                "select * from(\n"+
                "select ydks.*,\n" +
                "       jssj.nsrsbh js_nsrsbh,\n" +
                "       jssj.dh_1 js_tdzdh,\n" +
                "       jssj.tdsyqrmc js_nsrmc,\n" +
                "       jssj.zytdmj1 js_tdzmj,\n" +
                "       jssj.tdzyz js_tdzyz,\n" +
                "       jssj.jsse js_nynse,\n" +
                "       case\n" +
                "         when ydks.tdzmj = jssj.zytdmj1 and ydks.nynse = jssj.jsse then\n" +
                "          '正常'\n" +
                "         else\n" +
                "          '异常'\n" +
                "       end fxjg\n" +
                "  from gtxx_ydks ydks, gtxx_jssj jssj\n" +
                " where ydks.nsrsbh = jssj.nsrsbh)t\n"+
                "where t.fxjg='正常'\n";
       // List<Map<String, Object>> fgcjxxes = persistenceDAO.findAllBySql(fgcjxxSql, params);
        List results = persistenceDAO.findAllBySql(fgcjxxSql, params);
        for (int i = 0; i < results.size(); i++) {
            Map map = (Map) results.get(i);
            map.put("lxrdz", getFullAddress((String) map.get("lxrdzXzqhdsDm"), (String) map.get("lxrdzXzqhxqDm"), (String) map.get("lxrdzJdxzDm"), (String) map.get("lxrdzSqjqDm"), (String) map.get("lxrdzDlxdDm"), (String) map.get("lxrdzLh")));
            map.put("zldz", getFullAddress((String) map.get("xzqhdszDm"), (String) map.get("xzqhxqDm"), (String) map.get("jdxzDm"), (String) map.get("zldzSqjqDm"), (String) map.get("zldzDlxdDm"), (String) map.get("zldzLh")));
        }
        httpModel.setOutMsg(AOSJson.toGridJson(AOSUtils.list2Map(results)));
    }
}
